﻿using System;
using System.Data;
using System.IO;
using System.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace Citi.Import.Util
{
    public class ExcelReader
    {
        public DataTable GetData(string excelPath, string sheetName = null, bool ignoreNullRows = false, int ignoreFirstLines = 0, int ignoreFirstColumns = 0)
        {
            IWorkbook workbook = GetWorkbook(excelPath, ref sheetName);

            var sheet = workbook.GetSheet(sheetName);
            var headerRow = sheet.GetRow(ignoreFirstLines);
            int cellCount = headerRow.LastCellNum;

            var table = new DataTable();

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                var columnName = headerRow.GetCell(i).StringCellValue;
                var countColumns = table.Columns.Cast<DataColumn>().Count(x => x.ColumnName.StartsWith(columnName));

                if (countColumns > 0)
                    columnName += countColumns;

                table.Columns.Add(new DataColumn(columnName));
            }

            if (ignoreFirstColumns > 0)
            {
                for (int i = 0; i < ignoreFirstColumns - 1; i++)
                    table.Columns.RemoveAt(i);
            }

            for (var i = ignoreFirstLines + 1; i <= sheet.LastRowNum; i++)
            {
                var row = sheet.GetRow(i);
                if (row == null || row.Cells.Count() != table.Columns.Count) continue;

                var dataRow = table.NewRow();

                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j + row.FirstCellNum).ToString();
                }
                table.Rows.Add(dataRow);
            }

            return table;
        }

        public IWorkbook GetWorkbook(string excelPath, ref string sheetName)
        {
            if (string.IsNullOrEmpty(sheetName))
                throw new ArgumentException("sheetName");

            return excelPath.EndsWith("xlsx")
                ? (IWorkbook)new XSSFWorkbook(new FileStream(excelPath, FileMode.Open, FileAccess.Read))
                : new HSSFWorkbook(new FileStream(excelPath, FileMode.Open, FileAccess.Read));
        }

        public static void ExportDataTableToExcel(DataTable dtSource, string strFileName, string sheetNameToSave)
        {
            // create workbook
            var workbook = new XSSFWorkbook();
            // the table named mySheet
            var sheet = (XSSFSheet)workbook.CreateSheet(sheetNameToSave);
            // create the first row
            var dataRow = (XSSFRow)sheet.CreateRow(0);
            foreach (DataColumn column in dtSource.Columns)
            {
                // create the cells in the first row, and add data into these cells circularly
                dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            }
            //create rows on the basis of data from datatable(not including table header), and add data into cells in every row
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                dataRow = (XSSFRow)sheet.CreateRow(i + 1);
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
                }
            }
            using (var ms = new MemoryStream())
            {
                using (var fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {

                    workbook.Write(fs);// write mySheet table in xls document and save it
                }
            }
        }
    }
}